package com.oyb.dao;

import com.oyb.entity.Classes;
import com.oyb.entity.Page;
import com.oyb.util.ConnectionUtils;
import jdk.nashorn.internal.runtime.arrays.IteratorAction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassDao {
    /*
    查询所有班级——查询
     */
    public List<Classes> queryAllClasses() {
        List<Classes> allclasses = new ArrayList<>();
        Connection connection = ConnectionUtils.getconnection();
        String sql = "select * from classes";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Classes classes = new Classes();
                classes.setId(resultSet.getInt("id"));
                classes.setClass_name(resultSet.getString(2));
                classes.setClass_count(resultSet.getInt(3));
                allclasses.add(classes);
            }
            return allclasses;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close( statement,connection, resultSet);
        }
        return null;
    }

    //新增班级——插入
    public int insertclass(Classes classes) {
        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "insert into classes value(?,?,?)";
        try {
            connection = ConnectionUtils.getconnection();
            statement = connection.prepareStatement(sql);
            statement.setObject(1, classes.getId());
            statement.setObject(2, classes.getClass_name());
            statement.setObject(3, classes.getClass_count());
            return statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return 0;
    }

    //根据id删除班级
    public int delclassesbyid(String[] ids) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = ConnectionUtils.getconnection();
            String sql1 = "delete from classes where id in (";
            StringBuilder sql = new StringBuilder(sql1);
            for (int i = 0; i < ids.length; i++) {
                if (i > 0) {
                    sql.append(",?");
                } else {
                    sql.append("?");
                }
            }
            sql.append(")");
            System.out.println(sql);
            statement = connection.prepareStatement(String.valueOf(sql));
            for (int i = 0; i < ids.length; i++) {
                statement.setObject(i + 1, ids[i]);
            }
            //执行
            return statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return 0;
    }

    //根据id查询单个班级
    public Classes queryClasses(String id) {
        Connection connection = ConnectionUtils.getconnection();
        String sql = "select * from classes where id=?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setObject(1,id);
            resultSet = statement.executeQuery();
            Classes classes = new Classes();
            while (resultSet.next()) {
                classes.setId(resultSet.getInt("id"));
                classes.setClass_name(resultSet.getString("class_name"));
                classes.setClass_count(resultSet.getInt("class_count"));
            }
            return classes;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return null;
    }
    //根据id修改班级
    public int updateclass(Classes classes){
        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "update classes set class_name = ? where id = ?";
        try {
            connection = ConnectionUtils.getconnection();
            statement = connection.prepareStatement(sql);
            statement.setObject(1,classes.getClass_name());
            statement.setObject(2, classes.getId());
            return statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return 0;
    }

    //根据添加学生传递过来的班级id修改班级人数,为后面删除学生减少人数铺垫，可以再加个类型来判断是增加还是减少人数
    public int updateclasscount(Integer id,int count,Integer type) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "update classes set class_count = class_count"+(type > 0 ? "+" : "-")+"? where id = ?";
        try {
            connection = ConnectionUtils.getconnection();
            statement = connection.prepareStatement(sql);
            statement.setObject(1,count);
            statement.setObject(2, id);
            return statement.executeUpdate();
        }finally {
            ConnectionUtils.close(statement,connection,null);
        }
    }

    //分页查询——查询班级总记录条数
    public int querypagesum() throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "select count(*) from classes";
        try {
            connection = ConnectionUtils.getconnection();
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1);
            }
        }finally {
            ConnectionUtils.close(statement,connection,null);
        }
        return 0;
    }
    //分页查询——查询班级信息,每次按照limit的两个参数来查询对应的班级
    public List<Classes> queryclasseslist(Page p){
        List<Classes> classeslist = new ArrayList<>();
        Connection connection = ConnectionUtils.getconnection();
        String sql = "select * from classes limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setObject(1,(p.getPage()-1)*p.getPageSize());
            statement.setObject(2,p.getPageSize());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Classes classes = new Classes();
                classes.setId(resultSet.getInt("id"));
                classes.setClass_name(resultSet.getString(2));
                classes.setClass_count(resultSet.getInt(3));
                classeslist.add(classes);
            }
            return classeslist;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            ConnectionUtils.close( statement,connection, resultSet);
        }
        return null;
    }

}